package com.naiterui.ehp.bs.recommend.repository.mysql;

import java.util.Collection;
import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.naiterui.common.repository.core.BaseJpaRepository;
import com.naiterui.ehp.bs.recommend.bean.DrMedicationRecomInfo;
import com.naiterui.ehp.bs.recommend.bean.MedicationRecom;

@Repository
public interface IMedicationRecomInfoRepository extends BaseJpaRepository<DrMedicationRecomInfo, Long> {

    DrMedicationRecomInfo findFirstByRecommendId(Long recommendId);

    List<DrMedicationRecomInfo> findByRecommendIdIn(Collection<Long> recommendIdSet);

    @Modifying
    @Query(value = "update dr_medication_recom_info set doctor_sign_status = ?1, changed_at=now(),changed_by='signCallback' where recommend_id = ?2", nativeQuery = true)
    void updateDoctorSignStatus(Integer status, Long recomId);

    @Modifying
    @Query(value = "update dr_medication_recom_info set doctor_sign_status = ?1, doctor_sign_time = ?2, changed_at=now(),changed_by='signCallback' where recommend_id = ?3", nativeQuery = true)
    void updateDoctorSignStatus(Integer status, Date signTime, Long recomId);

    @Modifying
    @Query(value = "update dr_medication_recom_info set pharmacist_sign_status = ?1, changed_at=now(),changed_by='signCallback' where recommend_id = ?2", nativeQuery = true)
    void updatePharmacistSignStatus(Integer status, Long recomId);

    @Modifying
    @Query(value = "update dr_medication_recom_info set pharmacist_sign_status = ?1, pharmacist_sign_time = ?2, changed_at=now(),changed_by='signCallback' where recommend_id = ?3", nativeQuery = true)
    void updatePharmacistSignStatus(Integer status, Date signTime, Long recomId);

    @Modifying
    @Query(value = "update dr_medication_recom_info set allocate_pharmacist_sign_status = ?1, changed_at=now(),changed_by='signCallback' where recommend_id = ?2", nativeQuery = true)
    void updateAllocatePharmacistSignStatus(Integer status, Long recomId);

    @Modifying
    @Query(value = "update dr_medication_recom_info set allocate_pharmacist_sign_status = ?1, allocate_pharmacist_sign_time = ?2, changed_at=now(),changed_by='signCallback' where recommend_id = ?3", nativeQuery = true)
    void updateAllocatePharmacistSignStatus(Integer status, Date signTime, Long recomId);

    Integer countByDoctorIdAndCheckingStatus(Long doctorId, Integer checkingStatus);

    Integer countByDoctorIdAndCheckingStatusIn(Long doctorId, Collection<Integer>checkingStatus);

    @Query(value = "SELECT dmri FROM DrMedicationRecomInfo dmri JOIN MedicationRecom dmr ON dmri.recommendId=dmr.id JOIN MedicationRecomDetail dmrd ON dmri.recommendId=dmrd.recommendId WHERE dmr.doctorId = ?1 " +
        " AND dmr.forceRecom <>  " + MedicationRecom.FORCE_RECOM_STATUS_NOT_SEND
        + " AND (dmri.patientName LIKE ?2 OR dmrd.commonName LIKE ?2) GROUP BY dmri.recommendId ORDER BY dmri.createdAt DESC")
    Page<DrMedicationRecomInfo> searchByDoctorIdAndPatientName(Long doctorId,String patientName,Pageable pageable);

    @Query(value = " SELECT info FROM MedicationRecom  recom ,DrMedicationRecomInfo info   WHERE recom.id = info.recommendId " +
        " AND recom.forceRecom <>  " + MedicationRecom.FORCE_RECOM_STATUS_NOT_SEND
            + " AND recom.doctorId = ?1 order by info.createdAt desc")
    Page<DrMedicationRecomInfo> searchByDoctorId(Long doctorId, Pageable pageable);

    @Query(value = "SELECT info FROM MedicationRecom  recom ,DrMedicationRecomInfo info   WHERE recom.id = info.recommendId " +
            " AND recom.type <> " + MedicationRecom.RECOM_TYPE_VIDEO + " AND recom.forceRecom <>  " + MedicationRecom.FORCE_RECOM_STATUS_NOT_SEND
            + " AND recom.doctorId = ?1 AND info.checkingStatus=3 order by info.createdAt desc")
    Page<DrMedicationRecomInfo> searchMedicationInvalidAuditNotPass(Long doctorId, Integer status, Pageable pageable);

    @Query(value = "SELECT dmri FROM DrMedicationRecomInfo dmri JOIN MedicationRecom dmr ON dmri.recommendId=dmr.id JOIN RecomAuditLog ral ON dmri.recommendId=ral.recomId WHERE dmr.doctorId = ?1 AND dmr.status=0 AND dmr.expireAt < now() AND dmr.type!=3 AND dmr.forceRecom !=0 AND ral.status=1 ORDER BY dmri.createdAt DESC")
    Page<DrMedicationRecomInfo> searchMedicationInvalidPayOvertime(Long doctorId, Integer status, Pageable pageable);

    @Query(value = "SELECT dmri FROM DrMedicationRecomInfo dmri JOIN MedicationRecom dmr ON dmri.recommendId=dmr.id JOIN RecomAuditLog ral ON dmri.recommendId=ral.recomId WHERE dmr.doctorId = ?1 AND ((ral.status=0 AND dmr.expireAt < now()) OR ral.status=3) AND dmr.type!=3 AND dmr.forceRecom !=0 ORDER BY dmri.createdAt DESC")
    Page<DrMedicationRecomInfo> searchMedicationInvalidAuditOvertime(Long doctorId, Integer status, Pageable pageable);

    @Query(value = "SELECT dmri FROM DrMedicationRecomInfo dmri JOIN MedicationRecom dmr ON dmri.recommendId=dmr.id JOIN RecomAuditLog ral ON dmri.recommendId=ral.recomId WHERE dmr.doctorId = ?1 AND ((dmr.status=0 AND dmr.expireAt < now()) OR dmri.checkingStatus=3 OR dmri.checkingStatus=4 OR ral.status=3 OR (ral.status=0 AND dmr.expireAt < now())) AND dmr.type!=3 AND dmr.forceRecom !=0 ORDER BY dmri.createdAt DESC")
    Page<DrMedicationRecomInfo> searchMedicationInvalidList(Long doctorId, Integer status, Pageable pageable);

    @Query(value = "SELECT dmri FROM DrMedicationRecomInfo dmri JOIN MedicationRecom dmr ON dmri.recommendId=dmr.id JOIN RecomAuditLog ral ON dmri.recommendId=ral.recomId WHERE dmr.doctorId = ?1 AND dmr.inquirerId = ?2 AND dmri.checkingStatus=2 AND dmr.type<> " + MedicationRecom.RECOM_TYPE_VIDEO + " AND dmr.forceRecom <> " + MedicationRecom.FORCE_RECOM_STATUS_NOT_SEND +" ORDER BY dmri.createdAt DESC")
    Page<DrMedicationRecomInfo> searchByDoctorIdAndInquirerIdAndCheckingStatus(Long doctorId, Long inquirerId, Pageable pageable);
}
